﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aspose.Cells;
using System.Data;
using System.Web;

namespace TY.Utilities
{
    public class AsposeExcel
    {
        public static WorksheetCollection OpenExcel(string filePath)
        {
            Workbook wb = new Workbook(filePath);
            return wb.Worksheets;

        }
        public static void ExportTemplateData(string templatePath, string exportFileName, DataTable dt)
        {
            HttpContext curContext = HttpContext.Current;
            Aspose.Cells.WorkbookDesigner designer = new WorkbookDesigner();
            //给模板对象设置数据源  
            designer.SetDataSource(dt);
            //excel模板的地址  
            designer.Open(templatePath);
            designer.Process();//全自动赋值  
            designer.Workbook.Save(curContext.Response, exportFileName, ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
            
        }
        public static void ExportTemplateData(string templatePath, string exportFileName, List<DataTable> dtList, List<DictionaryEntry> dataList)
        {
            HttpContext curContext = HttpContext.Current;
            Aspose.Cells.WorkbookDesigner designer = new WorkbookDesigner();
            //excel模板的地址  
            designer.Open(templatePath);
            //给模板对象设置数据源  
            foreach (var item in dtList)
            {
                designer.SetDataSource(item);
            }
            foreach (var item in dataList)
            {
                designer.SetDataSource(item.Key.ToString(), item.Value);
            }
            designer.Process();//全自动赋值  
            designer.Workbook.Save(curContext.Response, exportFileName, ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
            designer = null;
        }
        public static void ExportLocalTemplateData(string templatePath, string exportFileName, List<DataTable> dtList)
        {
            HttpContext curContext = HttpContext.Current;
            Aspose.Cells.WorkbookDesigner designer = new WorkbookDesigner();
            //excel模板的地址  
            designer.Open(templatePath);
            //给模板对象设置数据源  
            foreach (var item in dtList)
            {
                designer.SetDataSource(item);
            }
            designer.Process();//全自动赋值  
            designer.Workbook.Save(exportFileName, new XlsSaveOptions(SaveFormat.Excel97To2003));
            designer = null;
        }
        public static void ExportLocalTemplateData(string templatePath, string exportFileName, List<DataTable> dtList, List<DictionaryEntry> dataList)
        {
            HttpContext curContext = HttpContext.Current;
            Aspose.Cells.WorkbookDesigner designer = new WorkbookDesigner();
            //excel模板的地址  
            designer.Open(templatePath);
            //给模板对象设置数据源  
            foreach (var item in dtList)
            {
                designer.SetDataSource(item);
            }
            foreach (var item in dataList)
            {
                designer.SetDataSource(item.Key.ToString(), item.Value);
            }
        
            designer.Process();//全自动赋值  
            designer.Workbook.Save(exportFileName, new XlsSaveOptions(SaveFormat.Excel97To2003));
            designer = null;
        }
        public static void ExportTemplateData(System.IO.Stream stream, string exportFileName, List<DataTable> dtList, List<DictionaryEntry> dataList)
        {
            HttpContext curContext = HttpContext.Current;
            Aspose.Cells.WorkbookDesigner designer = new WorkbookDesigner();
            //excel模板的地址  
            designer.Open((System.IO.MemoryStream)stream);
            //给模板对象设置数据源  
            foreach (var item in dtList)
            {
                designer.SetDataSource(item);
            }
            foreach (var item in dataList)
            {
                designer.SetDataSource(item.Key.ToString(), item.Value);
            }
            designer.Process();//全自动赋值  
            designer.Workbook.Save(curContext.Response, exportFileName, ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
            designer = null;
        }

        public static string GetTemplateBase64(string templatePath, List<DataTable> dtList, List<DictionaryEntry> dataList)
        {
            HttpContext curContext = HttpContext.Current;
            Aspose.Cells.WorkbookDesigner designer = new WorkbookDesigner();
            //excel模板的地址  
            designer.Open(templatePath);
            //给模板对象设置数据源  
            foreach (var item in dtList)
            {
                designer.SetDataSource(item);
            }
            foreach (var item in dataList)
            {
                designer.SetDataSource(item.Key.ToString(), item.Value.ToString());
            }
            designer.Process();//全自动赋值  
            byte[] bt = designer.Workbook.SaveToStream().GetBuffer();
            string base64Str = Convert.ToBase64String(bt);
            return base64Str;
        }
        public static string GetTemplateBase64(System.IO.Stream stream, List<DataTable> dtList, List<DictionaryEntry> dataList)
        {
            HttpContext curContext = HttpContext.Current;
            Aspose.Cells.WorkbookDesigner designer = new WorkbookDesigner();
            //excel模板的地址  
            designer.Open((System.IO.MemoryStream)stream);
            //给模板对象设置数据源  
            foreach (var item in dtList)
            {
                designer.SetDataSource(item);
            }
            foreach (var item in dataList)
            {
                designer.SetDataSource(item.Key.ToString(), item.Value.ToString());
            }
            designer.Process();//全自动赋值  
            byte[] bt = designer.Workbook.SaveToStream().GetBuffer();
            string base64Str = Convert.ToBase64String(bt);
            return base64Str;
        }
        public static void ExportLocalTemplateData(System.IO.Stream stream, string exportFileName, List<DataTable> dtList, List<DictionaryEntry> dataList)
        {
            HttpContext curContext = HttpContext.Current;
            Aspose.Cells.WorkbookDesigner designer = new WorkbookDesigner();
            //excel模板的地址  
            designer.Open((System.IO.MemoryStream)stream);
            //给模板对象设置数据源  
            foreach (var item in dtList)
            {
                designer.SetDataSource(item);
            }
            foreach (var item in dataList)
            {
                designer.SetDataSource(item.Key.ToString(), item.Value.ToString());
            }
            designer.Process();//全自动赋值  

            designer.Workbook.Save(exportFileName, new XlsSaveOptions(SaveFormat.Excel97To2003));
            designer = null;
        }

        public static System.IO.Stream GetTemplateStream(string templatePath, List<DataTable> dtList, List<DictionaryEntry> dataList)
        {
            HttpContext curContext = HttpContext.Current;
            Aspose.Cells.WorkbookDesigner designer = new WorkbookDesigner();
            //excel模板的地址  
            designer.Open(templatePath);
            //给模板对象设置数据源  
            foreach (var item in dtList)
            {
                designer.SetDataSource(item);
            }
            foreach (var item in dataList)
            {
                designer.SetDataSource(item.Key.ToString(), item.Value);
            }
            designer.Process();//全自动赋值  
            return designer.Workbook.SaveToStream();
        }
    }
}
